﻿using System;
using System.IO;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

public class ExcelClass
{
    public static Excel.Application xlApp;
    public static Excel.Workbook xlWorkBook;
    public static Excel.Worksheet xlWorkSheet;    
    public static bool check = false;
    public static string Culture;

    #region Abre Documento Excel

    public static void OpenXls(string Xls)
    {
        #region Pede Documento Excel se não existir e copia para a Pasta Data

        if (!File.Exists(Application.StartupPath + "\\Data\\" + Xls))
        {
            string Path2 = Application.StartupPath + "\\Data\\" + Xls;
            OpenFileDialog Abrircomo = Openfile.OpenFile("Não existe um ficheiro original de Localizações. Escolha o ficheiro por favor.",
                                                "Excel Document XLS (*.xls)|*.xls|Excel Document XLSX(*.xlsx)|*.xlsx");

            if (Abrircomo.ShowDialog() == DialogResult.OK)
            {
                string Path = Abrircomo.FileName;
                File.Copy(Path, Path2, false);
                check = true;
            }
        }
        else { check = true; }

        #endregion

        if (check)
        {
            try
            {
                xlApp = new Excel.Application();
                xlApp.DisplayAlerts = false;

                xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath +
                 "\\Data\\" + Xls, 0, false, 5, "ap12cFBd", "ap12cFBd", true, Excel.XlPlatform.xlWindows, "\t", true, false, 0, false, 1, 0);
            }
            catch (Exception ex)
            {
                if (ex.Message.IndexOf("80040154") != -1)//Aplicação não instalada
                {
                    MessageBox.Show("A aplicação Excel não está instalada. Não é possível exportar dados! \nA aplicação irá reiniciar.");
                    Application.Restart();
                }
                if (ex.Message.IndexOf("80028018") != -1)//Cultura Regional Errada
                {
                    Culture = System.Threading.Thread.CurrentThread.CurrentCulture.Name;
                    System.Threading.Thread.CurrentThread.CurrentCulture =
                            System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

                    xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath +
                     "\\Data\\" + Xls, 0, false, 5, "ap12cFBd", "ap12cFBd", true, Excel.XlPlatform.xlWindows, "\t", true, false, 0, false, 1, 0);
                }
                else
                { MessageBox.Show(ex.Message); }
            }
        }
        else
        { MessageBox.Show("Ficheiro Excel em falta", "Erro...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }
    }

    #endregion

    #region Fecha Documento Excel

    public static void CloseXls(Excel.Workbook WorkBook2Close, string WorkBook2CloseName)
    {
        if (check)
        {
            WorkBook2Close.Close(false, Application.StartupPath + "\\Data\\" + WorkBook2CloseName, null);
            Marshal.ReleaseComObject(WorkBook2Close);
        }
    }

    #endregion

    #region Grava Documento Excel

    public static void SaveXls(Excel.Workbook oWorkBook, string newWorkBook)
    {
        if (check)
        {
            try
            {
                xlApp.DisplayAlerts = false;
                oWorkBook.SaveAs(Application.StartupPath +
                "\\Data\\" + newWorkBook, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing , Type.Missing, Type.Missing, Type.Missing,
                Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }
    }

    #endregion

    //Mostra Sheet
    public static void ShowSheet(Excel.Worksheet xlWorkSheet)
    { if (check) { xlWorkSheet.Visible = Excel.XlSheetVisibility.xlSheetVisible; } }

    //Esconde Sheet
    public static void HideSheet(Excel.Worksheet xlWorkSheet)
    { if (check) { xlWorkSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; } }
}
